#!/usr/bin/perl -w

use warnings;
use strict;
use DBI;
use Switch;
use Encode;

my $dbh = DBI->connect("DBI:mysql:maptrick:HOSTNAME;mysql_enable_utf8=1","DB_USER",'DB_PASSWORD');

#my $lastupdate = `date +"%s" -d "09:00"`;
my $lastupdate = `date -u +"%s" `;
if (time-$lastupdate < 0) { $lastupdate-=86400; }
$lastupdate -= 28800;

my $graphonly = 0;
if ($graphonly > 0) {
	 print "WARNING: graphonly enabled!\n\n";
}

my $startmaptrick = 1009843200; #New Years, 2002
$startmaptrick = 1009238400; #New Years, 2002 minus 1 wk
#my $startmaptrick = 1107388800; #Thu, 03 Feb 2005 00:00:00 GMT
my $starttraining = 1131577200;
my $rrdtoolcmd = "/sw/bin/rrdtool";
my $rrdpath = "/www/maptrick/rrds";
my $graphpath = "/www/maptrick/htdocs/graphs";
my $trainopts = "-w 500 -h 200 -u 1 -l 0 -X 0 -a PNG";
my $bigtrainopts = "-w 900 -h 500 -u 1 -l 0 -X 0 -a PNG";
my $graphopts = "-w 500 -h 200 -X 0 -A -a PNG";
my $biggraphopts = "-w 900 -h 500 -X 0 -A -a PNG";
# -x DAY:1:WEEK:1:WEEK:1:604800:Wk\\ %V
my $comment = "COMMENT:\"\\s\" COMMENT:\"\\s\" COMMENT:\"\\s\" COMMENT:\"Visit http\\://websec.science.uva.nl/~lhoek/allgraphs.php for more graphs!\\c\"";
#my $lhrule = "HRULE:8#C090FF:\"1 level\" HRULE:40#90C0FF:\"2 levels\" HRULE:168#00FFFF:\"3 levels\" HRULE:680#00FF00:\"4 levels\" HRULE:2728#FFFF00:\"5 levels\" HRULE:10920#FFCC00:\"6 levels\" HRULE:19112#FF9900:\"7 levels\" HRULE:27304#FF0000:\"8 levels\" HRULE:35496#800080:\"9 levels\" HRULE:43688#FF43CC:\"10 levels\" HRULE:51880#000000:\"11 levels\"";

my $sth = $dbh->prepare("SELECT * FROM seasons");

$sth->execute;

my %expansions;

while (my @result = $sth->fetchrow()) {
        for (my $i = 1; $i <= 25; $i++) {
                $expansions{$result[0]}{$result[$i]} = $i if !defined $expansions{$result[0]}{$result[$i]};
        }
}

$sth = $dbh->prepare("SELECT LeagueID, ActiveUsers, WaitingUsers, EnglishName, NumberOfTeams FROM leagues_daily WHERE LeagueID != 255");

$sth->execute;

my $globalactive = 0;
my $globalwaiting = 0;
my $globalinterested = 0;

while (my @result = $sth->fetchrow()) {
	next if $result[0] == 255;
	
	my $id = $result[0];
	my $active = $result[1];
	my $waiting = $result[2];
	my $interested = $active + $waiting;
	my $name = $result[3];
#	$name = pack("C*", unpack("U0U*", $name));
#	utf8::decode($name);
#	utf8::decode($name);
	my $slots = $result[4];
	my $starttime = $startmaptrick;
	my $season11 = 972165600;

        my $vrule = "";
        for (my $i = 1; $i <= 12; $i++) {
                my $color = "";
                if (defined($expansions{$id}{$i})) {
                        switch ($i) {
                                case 1  {$color = "#C090FF"}
                                case 2  {$color = "#90C0FF"}
                                case 3  {$color = "#00FFFF"}
                                case 4  {$color = "#00FF00"}
                                case 5  {$color = "#FFFF00"}
                                case 6  {$color = "#FFCC00"}
                                case 7  {$color = "#FF9900"}
                                case 8  {$color = "#FF0000"}
                                case 9  {$color = "#800080"}
                                case 10 {$color = "#FF43CC"}
                                case 11 {$color = "#000000"}
				case 12 {$color = "#0000FF"}
                                else    {die "undefined size!"}
                        }
                        my $newseason = $season11 + (($expansions{$id}{$i}-11)*16*7*24*60*60);
                        $vrule = $vrule . "VRULE:$newseason$color:\"Added div. $i\" ";
                }
        }

	if ($id >=50)	{ $starttime += (16*7*86400); }
	if ($id >=59)	{ $starttime += (16*7*86400); }
	if ($id >=61)	{ $starttime += (16*7*86400); }
	if ($id >=66)	{ $starttime += (16*7*86400*2); }
	if ($id >=72)	{ $starttime += (16*7*86400); }
	if ($id >=79)	{ $starttime += (16*7*86400); }
	if ($id >=91)	{ $starttime += (16*7*86400); }
	if ($id >=98)	{ $starttime += (16*7*86400); }
	if ($id >=104)	{ $starttime += (16*7*86400); }
	if ($id >=110)	{ $starttime += (16*7*86400); }
	if ($id >=117)	{ $starttime += (16*7*86400); }
	if ($id >=123)	{ $starttime += (16*7*86400); }
	if ($id >=127)	{ $starttime += (16*7*86400); }
	if ($id ==127)	{ $starttime += (16*7*86400); }
	if ($id >=130)	{ $starttime += (16*7*86400); }
	if ($id >=133)	{ $starttime += (16*7*86400); }
	if ($id >=135)	{ $starttime += (16*7*86400*2); }
	if ($id >=139)	{ die ("fix starttime!"); }

	my $hrule = "";	
	for (my $i = 130; $i > 0; $i-=10) {
		my $color = "FF0000";
		if ($i%20) {$color = "FF8800";}
		my $perc = $slots * ($i/100);
		$hrule = $hrule . "HRULE:$perc#$color:\"$i% Full\" ";
	}
	$globalactive+=$active;
	$globalwaiting+=$waiting;

	my $cdefsnplots = "CDEF:tot=act,wait,+ AREA:tot#0000FF:\"Total users ($interested)\" AREA:act#00FF00:\"Active users ($active)\" LINE2:act#0000FF";

	#print "/$id\n";
	`$rrdtoolcmd update $rrdpath/$id.rrd N:$active:$waiting` unless ($graphonly);
	`$rrdtoolcmd graph $graphpath/$id.png --title="$name" $graphopts -s $starttime DEF:act=$rrdpath/$id.rrd:active:AVERAGE DEF:wait=$rrdpath/$id.rrd:waiting:AVERAGE $cdefsnplots $hrule $comment`;
	`$rrdtoolcmd graph $graphpath/big$id.png --title="$name" $biggraphopts -s $starttime DEF:act=$rrdpath/$id.rrd:active:AVERAGE DEF:wait=$rrdpath/$id.rrd:waiting:AVERAGE $cdefsnplots $hrule $comment`;
	`$rrdtoolcmd graph $graphpath/$id-w.png --title="$name Waiting list" $graphopts -s $starttime DEF:wait=$rrdpath/$id.rrd:waiting:AVERAGE AREA:wait#FF0000:"Waiting users ($waiting)" $comment`;
	`$rrdtoolcmd graph $graphpath/big$id-w.png --title="$name Waiting list" $biggraphopts -s $starttime DEF:wait=$rrdpath/$id.rrd:waiting:AVERAGE AREA:wait#FF0000:"Waiting users ($waiting)" $comment`;
	rotate($id);
}

$sth = $dbh->prepare("SELECT sum(ActiveUsers), sum(WaitingUsers) FROM leagues_daily WHERE leagueid != 255");

$sth->execute;

while (my @result = $sth->fetchrow()) {
	$globalactive = $result[0];
	$globalwaiting = $result[1];
	$globalinterested = $globalactive + $globalwaiting;
}

my $cdefsnplots = "CDEF:tot=act,wait,+ AREA:tot#0000FF:\"Total users ($globalinterested)\" AREA:act#00FF00:\"Active users ($globalactive)\" LINE2:act#0000FF";

`$rrdtoolcmd update $rrdpath/global.rrd N:$globalactive:$globalwaiting` unless ($graphonly);
`$rrdtoolcmd graph $graphpath/global.png --title="World-wide" $graphopts -s $startmaptrick DEF:act=$rrdpath/global.rrd:active:AVERAGE DEF:wait=$rrdpath/global.rrd:waiting:AVERAGE $cdefsnplots $comment`;
`$rrdtoolcmd graph $graphpath/bigglobal.png --title="World-wide" $biggraphopts -s $startmaptrick DEF:act=$rrdpath/global.rrd:active:AVERAGE DEF:wait=$rrdpath/global.rrd:waiting:AVERAGE $cdefsnplots $comment`;
`$rrdtoolcmd graph $graphpath/global-w.png --title="World-wide waiting list" $graphopts -s $startmaptrick DEF:wait=$rrdpath/global.rrd:waiting:AVERAGE AREA:wait#FF0000:"Waiting users ($globalwaiting)" $comment`;
`$rrdtoolcmd graph $graphpath/bigglobal-w.png --title="World-wide waiting list" $biggraphopts -s $startmaptrick DEF:wait=$rrdpath/global.rrd:waiting:AVERAGE AREA:wait#FF0000:"Waiting users ($globalwaiting)" $comment`;


######################## TRAINING

$sth = $dbh->prepare("SELECT EnglishName, training.* FROM leagues_daily,training WHERE leagues_daily.leagueid = training.leagueid and leagues_daily.LeagueID != 255");
$sth->execute;

my $cdefs = 'CDEF:tot=gen,sta,sp,def,sco,cp,shooting,shortp,pm,goal,trupass,defpos,wingatt,+,+,+,+,+,+,+,+,+,+,+,+ CDEF:p0=gen,tot,/,100,* CDEF:p1=sta,tot,/,100,* CDEF:p2=sp,tot,/,100,* CDEF:p3=def,tot,/,100,* CDEF:p4=sco,tot,/,100,* CDEF:p5=cp,tot,/,100,* CDEF:p6=shooting,tot,/,100,* CDEF:p7=shortp,tot,/,100,* CDEF:p8=pm,tot,/,100,* CDEF:p9=goal,tot,/,100,* CDEF:p10=trupass,tot,/,100,* CDEF:p11=defpos,tot,/,100,* CDEF:p12=wingatt,tot,/,100,*';

#my @colors = (
#"#FF9900",
#"#00CC99",
#"#CCFF66",
#"#FF6666",
#"#66CCFF",
#"#CC99FF",
#"#FF0000",
#"#3300FF",
#"#FF3399",
#"#CC00FF",
#"#FFFF33",
#"#33FF33",
#"#FF6600");

my @colors = (
"#808080", #General = 111 moet grijs zijn
"#008000", #Stamina = 010 moet donkergroen zijn
"#FFFF00", #Set Pieces = 220 moet geel zijn
"#0000FF", #Defending = 002 moet blauw zijn
"#FF0000", #Scoring = 200 moet rood zijn
"#00FFFF", #Cross Passes (Winger) = 022 moet cyan zijn
"#800000", #Shooting = 100 moet maroon zijn
"#FF00FF", #Short Passes = 202 moet fuchsia zijn
"#00FF00", #Playmaking = 020 moet groen zijn
"#000000", #Goaltending = 000 moet zwart zijn
"#800080", #Through Passes = 101 moet purple zijn
"#0000C0", #Defensive Positions = 001 moet navy zijn
"#808000"); #Wing Attacks = 110 blijft over

my $lw = 1;

while (my @result = $sth->fetchrow()) {
	my ($name, $id, $t0, $t1, $t2, $t3, $t4, $t5, $t6, $t7, $t8, $t9, $t10, $t11, $t12) = @result;
#	$name = pack("C*", unpack("U0U*", $name));
	my $total = $t0 + $t1 + $t2 + $t3 + $t4 + $t5 + $t6 + $t7 + $t8 + $t9 + $t10 + $t11 + $t12;
#	my ($p0, $p1, $p2, $p3, $p4, $p5, $p6, $p7, $p8, $p9, $p10, $p11, $p12) = sprintf(($t0/$total)*100,1);
	my $stacks = "AREA:p0$colors[0]:\"General ($t0)\":STACK AREA:p1$colors[1]:\"Stamina ($t1)\":STACK AREA:p2$colors[2]:\"Set Pieces ($t2)\":STACK AREA:p3$colors[3]:\"Defending ($t3)\":STACK AREA:p4$colors[4]:\"Scoring ($t4)\":STACK AREA:p5$colors[5]:\"Cross Passes (Winger) ($t5)\":STACK AREA:p6$colors[6]:\"Shooting ($t6)\":STACK AREA:p7$colors[7]:\"Short Passes ($t7)\":STACK AREA:p8$colors[8]:\"Playmaking ($t8)\":STACK AREA:p9$colors[9]:\"Goaltending ($t9)\":STACK AREA:p10$colors[10]:\"Through Passes ($t10)\":STACK AREA:p11$colors[11]:\"Defensive Positions ($t11)\":STACK AREA:p12$colors[12]:\"Wing Attacks ($t12)\":STACK";
	my $astacks = "AREA:gen$colors[0]:\"General ($t0)\":STACK AREA:sta$colors[1]:\"Stamina ($t1)\":STACK AREA:sp$colors[2]:\"Set Pieces ($t2)\":STACK AREA:def$colors[3]:\"Defending ($t3)\":STACK AREA:sco$colors[4]:\"Scoring ($t4)\":STACK AREA:cp$colors[5]:\"Cross Passes (Winger) ($t5)\":STACK AREA:shooting$colors[6]:\"Shooting ($t6)\":STACK AREA:shortp$colors[7]:\"Short Passes ($t7)\":STACK AREA:pm$colors[8]:\"Playmaking ($t8)\":STACK AREA:goal$colors[9]:\"Goaltending ($t9)\":STACK AREA:trupass$colors[10]:\"Through Passes ($t10)\":STACK AREA:defpos$colors[11]:\"Defensive Positions ($t11)\":STACK AREA:wingatt$colors[12]:\"Wing Attacks ($t12)\":STACK";
	my $plines = "LINE$lw:p0$colors[0]:\"General ($t0)\" LINE$lw:p1$colors[1]:\"Stamina ($t1)\" LINE$lw:p2$colors[2]:\"Set Pieces ($t2)\" LINE$lw:p3$colors[3]:\"Defending ($t3)\" LINE$lw:p4$colors[4]:\"Scoring ($t4)\" LINE$lw:p5$colors[5]:\"Cross Passes (Winger) ($t5)\" LINE$lw:p6$colors[6]:\"Shooting ($t6)\" LINE$lw:p7$colors[7]:\"Short Passes ($t7)\" LINE$lw:p8$colors[8]:\"Playmaking ($t8)\" LINE$lw:p9$colors[9]:\"Goaltending ($t9)\" LINE$lw:p10$colors[10]:\"Through Passes ($t10)\" LINE$lw:p11$colors[11]:\"Defensive Positions ($t11)\" LINE$lw:p12$colors[12]:\"Wing Attacks ($t12)\"";
	my $lines = "LINE$lw:gen$colors[0]:\"General ($t0)\" LINE$lw:sta$colors[1]:\"Stamina ($t1)\" LINE$lw:sp$colors[2]:\"Set Pieces ($t2)\" LINE$lw:def$colors[3]:\"Defending ($t3)\" LINE$lw:sco$colors[4]:\"Scoring ($t4)\" LINE$lw:cp$colors[5]:\"Cross Passes (Winger) ($t5)\" LINE$lw:shooting$colors[6]:\"Shooting ($t6)\" LINE$lw:shortp$colors[7]:\"Short Passes ($t7)\" LINE$lw:pm$colors[8]:\"Playmaking ($t8)\" LINE$lw:goal$colors[9]:\"Goaltending ($t9)\" LINE$lw:trupass$colors[10]:\"Through Passes ($t10)\" LINE$lw:defpos$colors[11]:\"Defensive Positions ($t11)\" LINE$lw:wingatt$colors[12]:\"Wing Attacks ($t12)\"";
	my $starttime = $starttraining;
	if ($id >=123) { $starttime = 1135766624; }
	if ($id > 126) { $starttime = 1145359408; }
	if ($id == 127 || $id > 129) { $starttime = 1155116868; }
	if ($id > 132) { $starttime = 1164019000; }
	if ($id > 134) { $starttime = 1184060000; }
	if ($id > 138) { die "fix starttime!"; }
	`$rrdtoolcmd update $rrdpath/t$id.rrd N:$t0:$t1:$t2:$t3:$t4:$t5:$t6:$t7:$t8:$t9:$t10:$t11:$t12` unless ($graphonly);

	`$rrdtoolcmd graph $graphpath/t$id.png --title="Training ($name (Cumulative %))" $trainopts -s $starttime DEF:gen=$rrdpath/t$id.rrd:t0:AVERAGE DEF:sta=$rrdpath/t$id.rrd:t1:AVERAGE DEF:sp=$rrdpath/t$id.rrd:t2:AVERAGE DEF:def=$rrdpath/t$id.rrd:t3:AVERAGE DEF:sco=$rrdpath/t$id.rrd:t4:AVERAGE DEF:cp=$rrdpath/t$id.rrd:t5:AVERAGE DEF:shooting=$rrdpath/t$id.rrd:t6:AVERAGE DEF:shortp=$rrdpath/t$id.rrd:t7:AVERAGE DEF:pm=$rrdpath/t$id.rrd:t8:AVERAGE DEF:goal=$rrdpath/t$id.rrd:t9:AVERAGE DEF:trupass=$rrdpath/t$id.rrd:t10:AVERAGE DEF:defpos=$rrdpath/t$id.rrd:t11:AVERAGE DEF:wingatt=$rrdpath/t$id.rrd:t12:AVERAGE $cdefs $stacks`;
	`$rrdtoolcmd graph $graphpath/trl$id.png --title="Training ($name (Relative %))" $trainopts -s $starttime DEF:gen=$rrdpath/t$id.rrd:t0:AVERAGE DEF:sta=$rrdpath/t$id.rrd:t1:AVERAGE DEF:sp=$rrdpath/t$id.rrd:t2:AVERAGE DEF:def=$rrdpath/t$id.rrd:t3:AVERAGE DEF:sco=$rrdpath/t$id.rrd:t4:AVERAGE DEF:cp=$rrdpath/t$id.rrd:t5:AVERAGE DEF:shooting=$rrdpath/t$id.rrd:t6:AVERAGE DEF:shortp=$rrdpath/t$id.rrd:t7:AVERAGE DEF:pm=$rrdpath/t$id.rrd:t8:AVERAGE DEF:goal=$rrdpath/t$id.rrd:t9:AVERAGE DEF:trupass=$rrdpath/t$id.rrd:t10:AVERAGE DEF:defpos=$rrdpath/t$id.rrd:t11:AVERAGE DEF:wingatt=$rrdpath/t$id.rrd:t12:AVERAGE $cdefs $plines`;
	`$rrdtoolcmd graph $graphpath/tal$id.png --title="Training ($name (Absolute))" $trainopts -s $starttime DEF:gen=$rrdpath/t$id.rrd:t0:AVERAGE DEF:sta=$rrdpath/t$id.rrd:t1:AVERAGE DEF:sp=$rrdpath/t$id.rrd:t2:AVERAGE DEF:def=$rrdpath/t$id.rrd:t3:AVERAGE DEF:sco=$rrdpath/t$id.rrd:t4:AVERAGE DEF:cp=$rrdpath/t$id.rrd:t5:AVERAGE DEF:shooting=$rrdpath/t$id.rrd:t6:AVERAGE DEF:shortp=$rrdpath/t$id.rrd:t7:AVERAGE DEF:pm=$rrdpath/t$id.rrd:t8:AVERAGE DEF:goal=$rrdpath/t$id.rrd:t9:AVERAGE DEF:trupass=$rrdpath/t$id.rrd:t10:AVERAGE DEF:defpos=$rrdpath/t$id.rrd:t11:AVERAGE DEF:wingatt=$rrdpath/t$id.rrd:t12:AVERAGE $cdefs $lines`;
	`$rrdtoolcmd graph $graphpath/ta$id.png --title="Training ($name (Absolute))" $trainopts -s $starttime DEF:gen=$rrdpath/t$id.rrd:t0:AVERAGE DEF:sta=$rrdpath/t$id.rrd:t1:AVERAGE DEF:sp=$rrdpath/t$id.rrd:t2:AVERAGE DEF:def=$rrdpath/t$id.rrd:t3:AVERAGE DEF:sco=$rrdpath/t$id.rrd:t4:AVERAGE DEF:cp=$rrdpath/t$id.rrd:t5:AVERAGE DEF:shooting=$rrdpath/t$id.rrd:t6:AVERAGE DEF:shortp=$rrdpath/t$id.rrd:t7:AVERAGE DEF:pm=$rrdpath/t$id.rrd:t8:AVERAGE DEF:goal=$rrdpath/t$id.rrd:t9:AVERAGE DEF:trupass=$rrdpath/t$id.rrd:t10:AVERAGE DEF:defpos=$rrdpath/t$id.rrd:t11:AVERAGE DEF:wingatt=$rrdpath/t$id.rrd:t12:AVERAGE $cdefs $astacks`;

	`$rrdtoolcmd graph $graphpath/bigt$id.png --title="Training ($name (Cumulative %))" $bigtrainopts -s $starttime DEF:gen=$rrdpath/t$id.rrd:t0:AVERAGE DEF:sta=$rrdpath/t$id.rrd:t1:AVERAGE DEF:sp=$rrdpath/t$id.rrd:t2:AVERAGE DEF:def=$rrdpath/t$id.rrd:t3:AVERAGE DEF:sco=$rrdpath/t$id.rrd:t4:AVERAGE DEF:cp=$rrdpath/t$id.rrd:t5:AVERAGE DEF:shooting=$rrdpath/t$id.rrd:t6:AVERAGE DEF:shortp=$rrdpath/t$id.rrd:t7:AVERAGE DEF:pm=$rrdpath/t$id.rrd:t8:AVERAGE DEF:goal=$rrdpath/t$id.rrd:t9:AVERAGE DEF:trupass=$rrdpath/t$id.rrd:t10:AVERAGE DEF:defpos=$rrdpath/t$id.rrd:t11:AVERAGE DEF:wingatt=$rrdpath/t$id.rrd:t12:AVERAGE $cdefs $stacks`;
#	`$rrdtoolcmd graph $graphpath/bigtrl$id.png --title="Training ($name (Relative %))" $bigtrainopts -s $starttime DEF:gen=$rrdpath/t$id.rrd:t0:AVERAGE DEF:sta=$rrdpath/t$id.rrd:t1:AVERAGE DEF:sp=$rrdpath/t$id.rrd:t2:AVERAGE DEF:def=$rrdpath/t$id.rrd:t3:AVERAGE DEF:sco=$rrdpath/t$id.rrd:t4:AVERAGE DEF:cp=$rrdpath/t$id.rrd:t5:AVERAGE DEF:shooting=$rrdpath/t$id.rrd:t6:AVERAGE DEF:shortp=$rrdpath/t$id.rrd:t7:AVERAGE DEF:pm=$rrdpath/t$id.rrd:t8:AVERAGE DEF:goal=$rrdpath/t$id.rrd:t9:AVERAGE DEF:trupass=$rrdpath/t$id.rrd:t10:AVERAGE DEF:defpos=$rrdpath/t$id.rrd:t11:AVERAGE DEF:wingatt=$rrdpath/t$id.rrd:t12:AVERAGE $cdefs $plines`;
#	`$rrdtoolcmd graph $graphpath/bigtal$id.png --title="Training ($name (Absolute))" $bigtrainopts -s $starttime DEF:gen=$rrdpath/t$id.rrd:t0:AVERAGE DEF:sta=$rrdpath/t$id.rrd:t1:AVERAGE DEF:sp=$rrdpath/t$id.rrd:t2:AVERAGE DEF:def=$rrdpath/t$id.rrd:t3:AVERAGE DEF:sco=$rrdpath/t$id.rrd:t4:AVERAGE DEF:cp=$rrdpath/t$id.rrd:t5:AVERAGE DEF:shooting=$rrdpath/t$id.rrd:t6:AVERAGE DEF:shortp=$rrdpath/t$id.rrd:t7:AVERAGE DEF:pm=$rrdpath/t$id.rrd:t8:AVERAGE DEF:goal=$rrdpath/t$id.rrd:t9:AVERAGE DEF:trupass=$rrdpath/t$id.rrd:t10:AVERAGE DEF:defpos=$rrdpath/t$id.rrd:t11:AVERAGE DEF:wingatt=$rrdpath/t$id.rrd:t12:AVERAGE $cdefs $lines`;
#	`$rrdtoolcmd graph $graphpath/bigta$id.png --title="Training ($name (Absolute))" $bigtrainopts -s $starttime DEF:gen=$rrdpath/t$id.rrd:t0:AVERAGE DEF:sta=$rrdpath/t$id.rrd:t1:AVERAGE DEF:sp=$rrdpath/t$id.rrd:t2:AVERAGE DEF:def=$rrdpath/t$id.rrd:t3:AVERAGE DEF:sco=$rrdpath/t$id.rrd:t4:AVERAGE DEF:cp=$rrdpath/t$id.rrd:t5:AVERAGE DEF:shooting=$rrdpath/t$id.rrd:t6:AVERAGE DEF:shortp=$rrdpath/t$id.rrd:t7:AVERAGE DEF:pm=$rrdpath/t$id.rrd:t8:AVERAGE DEF:goal=$rrdpath/t$id.rrd:t9:AVERAGE DEF:trupass=$rrdpath/t$id.rrd:t10:AVERAGE DEF:defpos=$rrdpath/t$id.rrd:t11:AVERAGE DEF:wingatt=$rrdpath/t$id.rrd:t12:AVERAGE $cdefs $astacks`;
}

$sth = $dbh->prepare("SELECT sum(t0)t0, sum(t1)t1, sum(t2)t2, sum(t3)t3, sum(t4)t4, sum(t5)t5, sum(t6)t6, sum(t7)t7, sum(t8)t8, sum(t9)t9, sum(t10)t10, sum(t11)t11, sum(t12)t12 from training");
$sth->execute;

my $globaltotal = 0;
my $global0 = 0;
my $global1 = 0;
my $global2 = 0;
my $global3 = 0;
my $global4 = 0;
my $global5 = 0;
my $global6 = 0;
my $global7 = 0;
my $global8 = 0;
my $global9 = 0;
my $global10 = 0;
my $global11 = 0;
my $global12 = 0;

while (my @result = $sth->fetchrow()) {
	$global0 = $result[0];
	$global1 = $result[1];
	$global2 = $result[2];
	$global3 = $result[3];
	$global4 = $result[4];
	$global5 = $result[5];
	$global6 = $result[6];
	$global7 = $result[7];
	$global8 = $result[8];
	$global9 = $result[9];
	$global10 = $result[10];
	$global11 = $result[11];
	$global12 = $result[12];
	$globaltotal = $global0+$global1+$global2+$global3+$global4+$global5+$global6+$global7+$global8+$global9+$global10+$global11+$global12;
}

my $defs = "DEF:gen=$rrdpath/tglobal.rrd:t0:AVERAGE DEF:sta=$rrdpath/tglobal.rrd:t1:AVERAGE DEF:sp=$rrdpath/tglobal.rrd:t2:AVERAGE DEF:def=$rrdpath/tglobal.rrd:t3:AVERAGE DEF:sco=$rrdpath/tglobal.rrd:t4:AVERAGE DEF:cp=$rrdpath/tglobal.rrd:t5:AVERAGE DEF:shooting=$rrdpath/tglobal.rrd:t6:AVERAGE DEF:shortp=$rrdpath/tglobal.rrd:t7:AVERAGE DEF:pm=$rrdpath/tglobal.rrd:t8:AVERAGE DEF:goal=$rrdpath/tglobal.rrd:t9:AVERAGE DEF:trupass=$rrdpath/tglobal.rrd:t10:AVERAGE DEF:defpos=$rrdpath/tglobal.rrd:t11:AVERAGE DEF:wingatt=$rrdpath/tglobal.rrd:t12:AVERAGE";

my $plots = "AREA:p0$colors[0]:\"General ($global0)\":STACK AREA:p1$colors[1]:\"Stamina ($global1)\":STACK AREA:p2$colors[2]:\"Set Pieces ($global2)\":STACK AREA:p3$colors[3]:\"Defending ($global3)\":STACK AREA:p4$colors[4]:\"Scoring ($global4)\":STACK AREA:p5$colors[5]:\"Cross Passes (Winger) ($global5)\":STACK AREA:p6$colors[6]:\"Shooting ($global6)\":STACK AREA:p7$colors[7]:\"Short Passes ($global7)\":STACK AREA:p8$colors[8]:\"Playmaking ($global8)\":STACK AREA:p9$colors[9]:\"Goaltending ($global9)\":STACK AREA:p10$colors[10]:\"Through Passes ($global10)\":STACK AREA:p11$colors[11]:\"Defensive Positions ($global11)\":STACK AREA:p12$colors[12]:\"Wing Attacks ($global12)\":STACK";

my $aplots = "AREA:gen$colors[0]:\"General ($global0)\":STACK AREA:sta$colors[1]:\"Stamina ($global1)\":STACK AREA:sp$colors[2]:\"Set Pieces ($global2)\":STACK AREA:def$colors[3]:\"Defending ($global3)\":STACK AREA:sco$colors[4]:\"Scoring ($global4)\":STACK AREA:cp$colors[5]:\"Cross Passes (Winger) ($global5)\":STACK AREA:shooting$colors[6]:\"Shooting ($global6)\":STACK AREA:shortp$colors[7]:\"Short Passes ($global7)\":STACK AREA:pm$colors[8]:\"Playmaking ($global8)\":STACK AREA:goal$colors[9]:\"Goaltending ($global9)\":STACK AREA:trupass$colors[10]:\"Through Passes ($global10)\":STACK AREA:defpos$colors[11]:\"Defensive Positions ($global11)\":STACK AREA:wingatt$colors[12]:\"Wing Attacks ($global12)\":STACK";

my $plines = "LINE$lw:p0$colors[0]:\"General ($global0)\" LINE$lw:p1$colors[1]:\"Stamina ($global1)\" LINE$lw:p2$colors[2]:\"Set Pieces ($global2)\" LINE$lw:p3$colors[3]:\"Defending ($global3)\" LINE$lw:p4$colors[4]:\"Scoring ($global4)\" LINE$lw:p5$colors[5]:\"Cross Passes (Winger) ($global5)\" LINE$lw:p6$colors[6]:\"Shooting ($global6)\" LINE$lw:p7$colors[7]:\"Short Passes ($global7)\" LINE$lw:p8$colors[8]:\"Playmaking ($global8)\" LINE$lw:p9$colors[9]:\"Goaltending ($global9)\" LINE$lw:p10$colors[10]:\"Through Passes ($global10)\" LINE$lw:p11$colors[11]:\"Defensive Positions ($global11)\" LINE$lw:p12$colors[12]:\"Wing Attacks ($global12)\"";

my $lines = "LINE$lw:gen$colors[0]:\"General ($global0)\" LINE$lw:sta$colors[1]:\"Stamina ($global1)\" LINE$lw:sp$colors[2]:\"Set Pieces ($global2)\" LINE$lw:def$colors[3]:\"Defending ($global3)\" LINE$lw:sco$colors[4]:\"Scoring ($global4)\" LINE$lw:cp$colors[5]:\"Cross Passes (Winger) ($global5)\" LINE$lw:shooting$colors[6]:\"Shooting ($global6)\" LINE$lw:shortp$colors[7]:\"Short Passes ($global7)\" LINE$lw:pm$colors[8]:\"Playmaking ($global8)\" LINE$lw:goal$colors[9]:\"Goaltending ($global9)\" LINE$lw:trupass$colors[10]:\"Through Passes ($global10)\" LINE$lw:defpos$colors[11]:\"Defensive Positions ($global11)\" LINE$lw:wingatt$colors[12]:\"Wing Attacks ($global12)\"";

`$rrdtoolcmd update $rrdpath/tglobal.rrd N:$global0:$global1:$global2:$global3:$global4:$global5:$global6:$global7:$global8:$global9:$global10:$global11:$global12` unless ($graphonly);
`$rrdtoolcmd graph $graphpath/tglobal.png --title="Training (Global (Cumulative %))" $trainopts -s $starttraining $defs $cdefs $plots`;
`$rrdtoolcmd graph $graphpath/trlglobal.png --title="Training (Global (Relative %))" $trainopts -s $starttraining $defs $cdefs $plines`;
`$rrdtoolcmd graph $graphpath/talglobal.png --title="Training (Global (Absolute))" $trainopts -s $starttraining $defs $cdefs $lines`;
`$rrdtoolcmd graph $graphpath/taglobal.png --title="Training (Global (Absolute))" $trainopts -s $starttraining $defs $cdefs $aplots`;
`$rrdtoolcmd graph $graphpath/bigtglobal.png --title="Training (Global (Cumulative %))" $bigtrainopts -s $starttraining $defs $cdefs $plots`;
`$rrdtoolcmd graph $graphpath/bigtrlglobal.png --title="Training (Global (Relative %))" $bigtrainopts -s $starttraining $defs $cdefs $plines`;
`$rrdtoolcmd graph $graphpath/bigtalglobal.png --title="Training (Global (Absolute))" $bigtrainopts -s $starttraining $defs $cdefs $lines`;
`$rrdtoolcmd graph $graphpath/bigtaglobal.png --title="Training (Global (Absolute))" $bigtrainopts -s $starttraining $defs $cdefs $aplots`;

my @types = (
	"gen",
	"sta",
	"sp",
	"def",
	"sco",
	"cp",
	"shooting",
	"shortp",
	"pm",
	"goal",
	"trupass",
	"defpos",
	"wingatt"
);

for (my $i = 0; $i < @types; $i++) {
	my $pline = "LINE$lw:p$i$colors[9]:\"Type $types[$i]\"";
	my $line = "LINE$lw:$types[$i]$colors[9]:\"Type $types[$i]\"";
	`$rrdtoolcmd graph $graphpath/trlglobalt$i.png --title="Training (Global (Relative %))" $trainopts -s $starttraining $defs $cdefs $pline`;
	`$rrdtoolcmd graph $graphpath/talglobalt$i.png --title="Training (Global (Absolute))" $trainopts -s $starttraining $defs $cdefs $line`;
}

##########################
$dbh->disconnect;
if ($graphonly > 0) {
	 print "WARNING: graphonly enabled!\n\n";
}
exit;

sub rotate {
	my $id = $_[0];
	$id++;
	$id--;
	my %a = (Active2 => $lastupdate-86400, Active14 => $lastupdate-14*86400, Active56 => $lastupdate-56*86400, Active112 => $lastupdate-112*86400, Active224 => $lastupdate-224*86400, Active336 => $lastupdate-336*86400);
	foreach my $a (keys %a) {
		my $stamp = $a{$a};
		$stamp--;
#		print "running $a for $id: $rrdtoolcmd fetch $rrdpath/$id.rrd AVERAGE -s $stamp -e $stamp\n";
		my $res = `$rrdtoolcmd fetch $rrdpath/$id.rrd AVERAGE -s $stamp -e $stamp`;
		#if ($id eq "global") $id = 0;
		my @res = split(/\n/, $res);
		foreach (@res) {
			next if (/nan/ or !/e\+/ or /e\-/);
			my @vals = split();
			my @num = split(/e\+/, $vals[1]);
			my $tmp = $num[1];
			my $act = int($num[0] * 10**$num[1]);
			my $s2h = $dbh->prepare("UPDATE history_n SET $a = $act WHERE LeagueID = $id");
			$s2h->execute;
		}
	}
	my %w = (Waiting2 => $lastupdate-86400, Waiting14 => $lastupdate-14*86400, Waiting56 => $lastupdate-56*86400, Waiting112 => $lastupdate-112*86400, Waiting224 => $lastupdate-224*86400, Waiting336 => $lastupdate-336*86400);
	foreach my $w (keys %w) {
		my $stamp = $w{$w};
		$stamp--;
		my $res = `$rrdtoolcmd fetch $rrdpath/$id.rrd AVERAGE -s $stamp -e $stamp`;
		#if ($id eq "global") $id = 0;
		my @res = split(/\n/, $res);
		foreach (@res) {
			next if (/nan/ or !/e\+/ or /e\-/);
			my @vals = split();
			my @num = split(/e\+/, $vals[2]);
			my $tmp = $num[1];
			my $wait = int($num[0] * 10**$num[1]);
			my $s2h = $dbh->prepare("UPDATE history_n SET $w = $wait WHERE LeagueID = $id");
			$s2h->execute;
		}
	}
}
